The present invention relates to inserts in a database table, and more specifically, to the updating of synopsis metadata for inserts in a database table.
Analytical databases are under enhanced pressure to increase performance of online transaction processing (OLTP) transactions, such as inserts of small numbers of rows. Users are requesting that their analytical databases always have the most up-to-date data available, meaning new data must constantly be trickle-fed into the analytical database as it arrives, rather than in bulk load operations as was previously the norm. Rather than relying on traditional database indexes, analytical databases also typically store minimum and maximum data value information for a number of rows physically located close to each other on disk, in order to enhance table scan performance by being able to skip over large numbers of rows that are guaranteed not to be able to match predicate conditions supplied in queries. This type of minimum and maximum data value information describing a subset of rows in the table is stored in zone maps, nearly-ordered maps, synopsis tables, or other similar structures.
The prior art document U.S. Pat. No. 6,973,452 B2 describes an approach for limiting scans of loosely ordered and/or grouped relations using nearly ordered maps. In this approach a large information space is divided into smaller information extents. These extents are annotated with statistics about the information they contain. When a search for information includes a restriction based on value, the desired value ranges can be compared to the value ranges of each extent. If the desired value range lies outside the range of the extent, then the extent cannot hold the desired value and does not need to be included in the search. The nearly ordered map table entries, each entry consisting of a table identifier, a column-index, a minimum data value, a maximum data value and an extent identifier, are grouped by column index, so that all the entries for the nth column of a table are grouped together in a single block.
However, the cost of creating or updating a synopsis entry (or nearly-ordered map entry, as referenced in U.S. Pat. No. 6,973,452 B2) in a synopsis table incurs significant overhead for inserts of a small number of rows, as the newly created or updated synopsis entry or entries must be updated and persisted to disk as part of the insert transaction. This persistence may be as part of a log record or by physically writing the synopsis entry itself out to disk. This problem in turn restricts the ability of analytical databases from meeting customers' demands for ingesting real-time data into their data warehouse, and so from being able to run analytical queries on real-time data.